﻿namespace Chain.DAL
{
    using Chain.DBUtility;
    using Chain.Model;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class SysShopMemLevel
    {
        public int Add(Chain.Model.SysShopMemLevel model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into SysShopMemLevel(");
            builder.Append("ShopID,MemLevelID,ClassDiscountPercent,ClassPointPercent,ClassRechargePointRate)");
            builder.Append(" values (");
            builder.Append("@ShopID,@MemLevelID,@ClassDiscountPercent,@ClassPointPercent,@ClassRechargePointRate)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ShopID", SqlDbType.Int, 4), new SqlParameter("@MemLevelID", SqlDbType.Int, 4), new SqlParameter("@ClassDiscountPercent", SqlDbType.Money, 8), new SqlParameter("@ClassPointPercent", SqlDbType.Money, 8), new SqlParameter("@ClassRechargePointRate", SqlDbType.Money, 8) };
            cmdParms[0].Value = model.ShopID;
            cmdParms[1].Value = model.MemLevelID;
            cmdParms[2].Value = model.ClassDiscountPercent;
            cmdParms[3].Value = model.ClassPointPercent;
            cmdParms[4].Value = model.ClassRechargePointRate;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool Delete(int ShopMemLevelID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from SysShopMemLevel ");
            builder.Append(" where ShopMemLevelID=@ShopMemLevelID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ShopMemLevelID", SqlDbType.Int, 4) };
            cmdParms[0].Value = ShopMemLevelID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool DeleteByMemLevelID(int MemLevelID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from SysShopMemLevel ");
            builder.Append(" where MemLevelID=@MemLevelID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@MemLevelID", SqlDbType.Int, 4) };
            cmdParms[0].Value = MemLevelID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool DeleteList(string ShopMemLevelIDlist)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from SysShopMemLevel ");
            builder.Append(" where ShopMemLevelID in (" + ShopMemLevelIDlist + ")  ");
            return (DbHelperSQL.ExecuteSql(builder.ToString()) > 0);
        }

        public bool Exists(int ShopMemLevelID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from SysShopMemLevel");
            builder.Append(" where ShopMemLevelID=@ShopMemLevelID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ShopMemLevelID", SqlDbType.Int, 4) };
            cmdParms[0].Value = ShopMemLevelID;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ShopMemLevelID,ShopID,MemLevelID,ClassDiscountPercent,ClassPointPercent,ClassRechargePointRate ");
            builder.Append(" FROM SysShopMemLevel ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" ShopMemLevelID,ShopID,MemLevelID,ClassDiscountPercent,ClassPointPercent,ClassRechargePointRate ");
            builder.Append(" FROM SysShopMemLevel ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT * FROM ( ");
            builder.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                builder.Append("order by T." + orderby);
            }
            else
            {
                builder.Append("order by T.ShopMemLevelID desc");
            }
            builder.Append(")AS Row, T.*  from SysShopMemLevel T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                builder.Append(" WHERE " + strWhere);
            }
            builder.Append(" ) TT");
            builder.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(builder.ToString());
        }

        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ShopMemLevelID", "SysShopMemLevel");
        }

        public Chain.Model.SysShopMemLevel GetModel(int ShopMemLevelID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ShopMemLevelID,ShopID,MemLevelID,ClassDiscountPercent,ClassPointPercent,ClassRechargePointRate from SysShopMemLevel ");
            strSql.Append(" where ShopMemLevelID=@ShopMemLevelID");
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@ShopMemLevelID", SqlDbType.Int, 4) };
            parameters[0].Value = ShopMemLevelID;
            return NewMethod(strSql, parameters);
        }

        public Chain.Model.SysShopMemLevel GetModel(int MemLevelID, int shopID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ShopMemLevelID,ShopID,MemLevelID,ClassDiscountPercent,ClassPointPercent,ClassRechargePointRate from SysShopMemLevel ");
            strSql.Append(" where MemLevelID=@MemLevelID and ShopID = @ShopID ");
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@MemLevelID", SqlDbType.Int, 4), new SqlParameter("@ShopID", SqlDbType.Int, 4) };
            parameters[0].Value = MemLevelID;
            parameters[1].Value = shopID;
            return NewMethod(strSql, parameters);
        }

        public int GetRechargePointRate(int memLevelID, int shopID)
        {
            int num;
            string sQLString = "select ClassRechargePointRate from SysShopMemLevel where MemLevelID = @MemLevelID and ShopID = @ShopID";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@MemLevelID", SqlDbType.Int, 4), new SqlParameter("@ShopID", SqlDbType.Int, 4) };
            cmdParms[0].Value = memLevelID;
            cmdParms[1].Value = shopID;
            try
            {
                SqlDataReader reader = DbHelperSQL.ExecuteReader(sQLString, cmdParms);
                if (reader.HasRows)
                {
                    reader.Read();
                    return reader.GetInt32(reader.GetOrdinal("ClassRechargePointRate"));
                }
                num = 0;
            }
            catch
            {
                throw;
            }
            return num;
        }

        public int GetRecordCount(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) FROM SysShopMemLevel ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            object single = DbHelperSQL.GetSingle(builder.ToString());
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        private static Chain.Model.SysShopMemLevel NewMethod(StringBuilder strSql, SqlParameter[] parameters)
        {
            Chain.Model.SysShopMemLevel level = new Chain.Model.SysShopMemLevel();
            DataSet set = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (set.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            if ((set.Tables[0].Rows[0]["ShopMemLevelID"] != null) && (set.Tables[0].Rows[0]["ShopMemLevelID"].ToString() != ""))
            {
                level.ShopMemLevelID = int.Parse(set.Tables[0].Rows[0]["ShopMemLevelID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["ShopID"] != null) && (set.Tables[0].Rows[0]["ShopID"].ToString() != ""))
            {
                level.ShopID = int.Parse(set.Tables[0].Rows[0]["ShopID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["MemLevelID"] != null) && (set.Tables[0].Rows[0]["MemLevelID"].ToString() != ""))
            {
                level.MemLevelID = int.Parse(set.Tables[0].Rows[0]["MemLevelID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["ClassDiscountPercent"] != null) && (set.Tables[0].Rows[0]["ClassDiscountPercent"].ToString() != ""))
            {
                level.ClassDiscountPercent = decimal.Parse(set.Tables[0].Rows[0]["ClassDiscountPercent"].ToString());
            }
            if ((set.Tables[0].Rows[0]["ClassPointPercent"] != null) && (set.Tables[0].Rows[0]["ClassPointPercent"].ToString() != ""))
            {
                level.ClassPointPercent = decimal.Parse(set.Tables[0].Rows[0]["ClassPointPercent"].ToString());
            }
            if ((set.Tables[0].Rows[0]["ClassRechargePointRate"] != null) && (set.Tables[0].Rows[0]["ClassRechargePointRate"].ToString() != ""))
            {
                level.ClassRechargePointRate = decimal.Parse(set.Tables[0].Rows[0]["ClassRechargePointRate"].ToString());
            }
            return level;
        }

        public bool Update(Chain.Model.SysShopMemLevel model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update SysShopMemLevel set ");
            builder.Append("ShopID=@ShopID,");
            builder.Append("MemLevelID=@MemLevelID,");
            builder.Append("ClassDiscountPercent=@ClassDiscountPercent,");
            builder.Append("ClassPointPercent=@ClassPointPercent,");
            builder.Append("ClassRechargePointRate=@ClassRechargePointRate");
            builder.Append(" where ShopMemLevelID=@ShopMemLevelID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ShopID", SqlDbType.Int, 4), new SqlParameter("@MemLevelID", SqlDbType.Int, 4), new SqlParameter("@ClassDiscountPercent", SqlDbType.Money, 8), new SqlParameter("@ClassPointPercent", SqlDbType.Money, 8), new SqlParameter("@ClassRechargePointRate", SqlDbType.Money, 8), new SqlParameter("@ShopMemLevelID", SqlDbType.Int, 4) };
            cmdParms[0].Value = model.ShopID;
            cmdParms[1].Value = model.MemLevelID;
            cmdParms[2].Value = model.ClassDiscountPercent;
            cmdParms[3].Value = model.ClassPointPercent;
            cmdParms[4].Value = model.ClassRechargePointRate;
            cmdParms[5].Value = model.ShopMemLevelID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}

